package com.scalar.chiptrack.sales.parser;

import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.ScalarException;

import java.io.File;
import java.io.IOException;
import java.io.BufferedReader;
import java.io.FileReader;
import java.util.*;

import jxl.Workbook;
import jxl.Sheet;
import jxl.Cell;
import jxl.read.biff.BiffException;


/**
 * User: Sujan
 * Date: Jan 8, 2007
 * Time: 11:55:52 AM
 */
public class CustomerParser
{
    private Logger m_parserLogger     = null;

    public CustomerParser() throws Exception
    {
        try
        {
            m_parserLogger = new Logger(  "./logs", "CustomerPorting", "Parser", "debug", 1024 * 1000, 1 );
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
    }
    public void execute() throws Exception
    {
        boolean success = false;
        printHeader();
        File customerFile = getFileForPorting(System.getProperty ( "customerPortingFile" ));
        log ( "Parsing Customer Porting File:" + customerFile.getName());
        System.out.println ( "Parsing Customer Porting File:" + customerFile.getName() );
        //Collection customerCollection = parseCustomerFile ( customerFile );
        ArrayList customerList = parseCustomerCSVFile( customerFile );//new ArrayList ( customerCollection );
        log ( "Parsing completed successfully..." );
        System.out.println ( "Parsing completed successfully.....");
        log ( "Porting Customer info..." );
        System.out.println ( "Porting Customer info..." );
        DBTransaction dbTransaction = null;
        try
        {
            dbTransaction = DBTransaction.getInstance();
            dbTransaction.begin();
            createUpdateCustomers(dbTransaction, customerList.iterator() );
            if ( dbTransaction != null )
            {
                dbTransaction.commit();
            }
            success = true;
        }
        catch ( ScalarException e )
        {
            log ( "Exception while porting customers:" + e.getRootCause() );
            e.printStackTrace();
            throw e;
        }
        finally
        {
            if ( dbTransaction != null )
            {
                if ( !success )
                {
                    dbTransaction.rollback();
                }
                   dbTransaction = null;
            }
        }
        if ( success ) //SEND PORTING STATUS MAIL
        {
            log ( "Porting Customer info completed successfully...." );
            System.out.println ( "Porting Customer info completed successfully...." );

        }
        else
        {
            log ( "**ERROR**Unable to port the customer information...." );
            System.out.println ( "**ERROR**Unable to port the customer information..." );
        }
        System.out.println ( "*** PLEASE SEE THE LOG FILE FOR DETAILED INFORMATION ***" );
    }
    private void printHeader()
    {
        String info = "\n\n+----------- Running Customer Porting on " + new java.util.Date( System.currentTimeMillis() ) + " -------------+\n" +
                 "\n+---------------------------------------------------------------+\n" +
                 "+                                                                \n" +
                 "+            Customer Porting - Beta          \n" +
                 "+                                                                \n" +
                 "+    Copy Right (C) 2007 - ScalarSoft Inc., All rights reserved.  \n" +
                 "+                                                                \n" +
                 "+---------------------------------------------------------------+\n" ;
        System.out.println ( info );
        log( info );
    }
    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + AdaptorConstants.LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    }
    private File getFileForPorting ( String customerPortingFile ) throws ScalarException
    {
        if ( customerPortingFile == null || customerPortingFile.trim().length() == 0 )
        {
            log ( "***ERROR***Customer Porting File Name not specified" );
            System.out.println ( "***ERROR***Customer Porting File Name not specified" );
            throw new ScalarException( "Customer Porting File Name not specified" );
        }
        File portingFile = new File ( customerPortingFile );
        if ( !portingFile.exists()  && !portingFile.isFile() )
        {
            log ( "Customer Porting File:" + customerPortingFile + " Does not exist" );
            throw new ScalarException( customerPortingFile + " does not exist" );
        }
        else
        {
            log ( "Customer Porting File:" + customerPortingFile + " Found" );
            System.out.println ( "Customer Porting File:" + customerPortingFile + " Found" );
        }
        return portingFile;
    }
    private Collection parseCustomerFile ( File customerPortingFile ) throws IOException, BiffException
    {
        Workbook workbook 	        = Workbook.getWorkbook( customerPortingFile );
        Sheet sheet 		        = workbook.getSheet( "Customer Data" );
        Cell[] cells 			    = null;
        CustomerInfo customerInfo   = null;
        Address billingAddress = null;
        Address shippingAddress = null;
        int noOfCells               = 0;
        int noOfRows                = sheet.getRows();
        HashMap customerMap         = new HashMap();
        for ( int rowCount = 1; rowCount < noOfRows; rowCount++ )
        {
            cells = sheet.getRow( rowCount );
            noOfCells = cells.length;
            if ( noOfCells > 0 )
            {
                String kill = cells[0].getContents().trim();
                if ( "1".equals( kill ) ) //IGNORING......
                {
                    log ( "IGNORING Customer for the Row:" + rowCount );
                    continue;
                }
            }
            if ( noOfCells > 1  )
            {
                if ( cells[1].getContents() != null && cells[1].getContents().trim().length() > 0 ) //Customer Name available
                {
                    customerInfo = (CustomerInfo)customerMap.get(cells[1].getContents().trim());
                    if ( customerInfo == null )
                    {
                        customerInfo = new CustomerInfo(cells[1].getContents().trim(), cells[1].getContents().trim());
                        customerMap.put ( customerInfo.getCustomerNumber(), customerInfo );
                    }
                }
                else
                {
                    log( "Customer Name Missing for the Row:" + rowCount );
                    continue;
                }
                billingAddress = new Address();
                shippingAddress = new Address();

                if ( noOfCells > 1 ) //billing billingAddress tag_name
                {
                    billingAddress.setTagName( cells[1].getContents().trim() );
                }

                if ( noOfCells > 7 ) //billing address1
                {
                    billingAddress.setAddress1( cells[7].getContents().trim() );
                }
                if ( noOfCells > 8 ) //billing address2
                {
                    billingAddress.setAddress2( cells[8].getContents().trim() );
                }
                if ( noOfCells > 9 ) //billing address3
                {
                    billingAddress.setAddress3( cells[9].getContents().trim() );
                }
                if ( noOfCells > 10 ) //billing zipcode
                {
                    billingAddress.setZipCode( cells[10].getContents().trim() );
                }
                if ( noOfCells > 11 ) //billing city
                {
                    billingAddress.setCity( cells[11].getContents().trim() );
                }
                if ( noOfCells > 12 ) //billing state
                {
                    billingAddress.setState( cells[12].getContents().trim() );
                }
                if ( noOfCells > 13 ) //billing country
                {
                    billingAddress.setCountry( cells[13].getContents().trim() );
                }
                if ( noOfCells > 14 ) //billing billingAddress contact person
                {
                    billingAddress.setContactPerson( cells[14].getContents().trim() );
                }
                if ( noOfCells > 15 ) //billing billingAddress contact person email
                {
                    billingAddress.setEmail( cells[15].getContents().trim() );
                }
                if ( noOfCells > 16 ) //billing billingAddress contact person phone
                {
                    billingAddress.setPhoneNumber( cells[16].getContents().trim() );
                }
                if ( noOfCells > 17 ) //billing billingAddress contact person fax
                {
                    billingAddress.setFaxNumber( cells[17].getContents().trim() );
                }
                if ( noOfCells > 18 ) //billing billingAddress tag name
                {
                    shippingAddress.setTagName( cells[18].getContents().trim() );
                }
                if ( noOfCells > 19 ) //is default shipping billingAddress
                {
                    shippingAddress.setDefaultAddress ( "1".equals ( cells[19].getContents().trim() ) ? "true" : "false" );
                }
                if ( noOfCells > 23 ) //shippingAddress address1
                {
                    shippingAddress.setAddress1( cells[23].getContents().trim() );
                }
                if ( noOfCells > 24 ) //shippingAddress address2
                {
                    shippingAddress.setAddress2( cells[24].getContents().trim() );
                }
                if ( noOfCells > 25 ) //shippingAddress address3
                {
                    shippingAddress.setAddress3( cells[25].getContents().trim() );
                }
                if ( noOfCells > 26 ) //shippingAddress zip code
                {
                    shippingAddress.setZipCode( cells[26].getContents().trim() );
                }
                if ( noOfCells > 27 ) //shippingAddress city
                {
                    shippingAddress.setCity( cells[27].getContents().trim() );
                }
                if ( noOfCells > 28 ) //shippingAddress state
                {
                    shippingAddress.setState( cells[28].getContents().trim() );
                }
                if ( noOfCells > 30 ) //shippingAddress country
                {
                    shippingAddress.setCountry( cells[30].getContents().trim() );
                }
                if ( noOfCells > 31 ) //delivery terms
                {
                    customerInfo.setDeliveryTerms( cells[31].getContents().trim() );
                }

                customerInfo.setBillingAddress( billingAddress );
                customerInfo.addShippingAddress(shippingAddress);
            }
            else
            {
                log ( "Could not found Customer Number Cell for the Row:" + (rowCount+1) );
                continue;
            }
        }
        return customerMap.values();
    }

    public static void main ( String[] agrs ) throws Exception
    {
        CustomerParser customerParser = new CustomerParser();
        customerParser.execute();
    }

    private void createUpdateCustomers ( DBTransaction dbTransaction, Iterator customerIterator ) throws ScalarException
    {
        final int defaultBillingAddressStatusCode = 1;
        final int defaultShippingAddressStatusCode = 2;
        CustomerInfo customerInfo   = null;
        int customerId              = -1;
        while ( customerIterator.hasNext() )
        {
            customerInfo = ( CustomerInfo )customerIterator.next();
            //int dbCustId = CustomerParserDAO.getCustomerId(dbTransaction,customerInfo.getCustomerNumber());
            customerId = CustomerParserDAO.saveCustomer( dbTransaction, customerInfo.getCustomerName(), customerInfo.getCustomerNumber(), customerInfo.getDeliveryTerms(), Constants.CUSTOMER_TYPE_CUSTOMER, customerInfo.getContactPerson(), customerInfo.isDistributor(), customerInfo.getRegion(), customerInfo.getShippingMethod(), customerInfo.getShippingAccountNumber());
            int addrsStatusCode = 0;
            if ( customerId > 0 )
            {
                ArrayList billingAddresses = new ArrayList();
                billingAddresses.add ( customerInfo.getBillingAddress() );
                addrsStatusCode = CustomerParserDAO.saveCustomerAddresses(dbTransaction, customerId, billingAddresses, "BILLING" );
                if ( (addrsStatusCode & defaultBillingAddressStatusCode) != defaultBillingAddressStatusCode ) //NO DEFAULT BILLING ADDRESS
                {
                    log ( "NO Default Billing Address Found For the Customer:" +  customerInfo.getCustomerName() );
                }

                addrsStatusCode = CustomerParserDAO.saveCustomerAddresses(dbTransaction, customerId, customerInfo.getShippingAddresses(), "SHIPPING" );

                if ( (addrsStatusCode & defaultShippingAddressStatusCode) != defaultShippingAddressStatusCode ) //NO DEFAULT SHIPPING ADDRESS
                {
                    log ( "NO Default Shipping Address Found For the Customer:" +  customerInfo.getCustomerName() );
                }
            }
            else
            {
                log ( "****ERROR***Could not create Customer:" + customerInfo.getCustomerName() );
                System.out.println ( "****ERROR***Could not create Customer:" + customerInfo.getCustomerName() );
            }
        }
    }
    private ArrayList parseCustomerCSVFile ( File customerPortingFile ) throws IOException
    {
        int rowNo = 0;
        int noOfColumns = 0;
        String[] columns = null;
        String delim = ",";
        CustomerInfo customerInfo   = null;
        Address address             = null;
        ArrayList customerList         = new ArrayList();
        BufferedReader br = new BufferedReader( new FileReader( customerPortingFile ) );
        String line = null;
        String customerNumber = null;
        String customerName = null;
        line = br.readLine();//IGNORING FIRST LINE
        while ( ( line = br.readLine() ) != null && line.length() > 0 )
        {
            rowNo++;
            //----
            String[] lineToken = line.split("\"");
            if (lineToken != null && lineToken.length > 1)
            {
                for ( int i=1; i < lineToken.length; i=i+2 )
                {
                    lineToken[i] = lineToken[i].replaceAll(",", "@");
                }
            }

            line = "";
            for (int i = 0; i < lineToken.length; i++)
            {
                line = line + lineToken[i];
            }
            //----
            line = line.replaceAll( "\"", "\\\"" );
            columns = line.split(delim);
            noOfColumns = columns.length;
            Address billingAddress = null;
            Address shippingAddress = null;
            if ( noOfColumns > 2 )
            {
                customerNumber = columns[0] != null ? columns[0].trim().replaceAll("@", ",") : null;
                customerName = columns[1] != null ? columns[1].trim().replaceAll("@", ",") : null;
                if (customerNumber != null && customerNumber.length() > 0)
                {
                    customerInfo = new CustomerInfo();
                    customerInfo.setCustomerNumber( customerNumber );
                    /*customerInfo = (CustomerInfo)customerMap.get(customerNumber.trim());
                    if ( customerInfo == null )
                    {
                        customerInfo = new CustomerInfo( customerName, customerNumber.trim() );
                        customerMap.put ( customerInfo.getCustomerNumber(), customerInfo );
                    }*/
                }
                else
                {
                    log( "IGNORING: Customer Number Missing for the Row:" + rowNo );
                    continue;
                }
                if ( customerName != null && customerName.length() > 0 )
                {
                    customerInfo.setCustomerName( customerName );
                    /*String tempCustNo = "TEMP_CUST#" + customerName;
                    customerInfo = (CustomerInfo)customerMap.get( tempCustNo );
                    if ( customerInfo == null )
                    {
                        customerInfo = new CustomerInfo( customerName, customerNumber );
                        customerMap.put ( tempCustNo, customerInfo );
                    }*/
                }
                else
                {
                    log( "IGNORING: Customer Name Missing for the Row:" + rowNo );
                    continue;
                }
                //}
                if ( "TRUE".equalsIgnoreCase( columns[3].trim().replaceAll("@", ",") ) ) //Inactive
                {
                    log( "IGNORING: Customer is Inactive for the Row:" + rowNo );
                    continue;
                }
                //Contact
                customerInfo.setContactPerson( columns[4].trim().replaceAll("@", ",") );
                billingAddress = new Address();
                customerInfo.setBillingAddress( billingAddress );
                billingAddress.setAddress1( customerInfo.getCustomerName() );
                billingAddress.setAddress2( columns[5].trim().replaceAll("@", ",") );
                billingAddress.setAddress3( columns[6].trim().replaceAll("@", ",") );
                billingAddress.setCity( columns[7].trim().replaceAll("@", ",") );
                billingAddress.setState( columns[8].trim().replaceAll("@", ",") );
                billingAddress.setZipCode( columns[9].trim().replaceAll("@", ",") );
                billingAddress.setCountry( columns[10].trim().replaceAll("@", ",") );
                billingAddress.setDefaultAddress( "true" );
                boolean ignoreAddress = false;
                for (int x = 12; x < 84 && noOfColumns >= 84; x++)
                {
                    int mod = x%8;
                    //if (columns[x] != null && columns[x].trim().length() > 0)
                    {
                        switch (mod)
                        {
                            case 4: //Ship to Name 1
                                if ( columns[x] != null && columns[x].trim().length() > 0 )
                                {
                                    shippingAddress = new Address();
                                    shippingAddress.setAddress1( columns[x].trim().replaceAll("@", ",") );
                                    if ( customerInfo.getShippingAddresses() == null || customerInfo.getShippingAddresses().size() == 0 )
                                    {
                                        shippingAddress.setDefaultAddress( "true" );
                                    }
                                    customerInfo.addShippingAddress( shippingAddress );
                                    ignoreAddress = false;
                                }
                                else
                                {
                                    ignoreAddress = true;
                                }
                                break;
                            case 5://Ship to Address 1-Line One
                                if ( !ignoreAddress )
                                    shippingAddress.setAddress2( columns[x].trim().replaceAll("@", ",") );
                                break;
                            case 6: //Ship to Address 1-Line Two
                                if ( !ignoreAddress )
                                    shippingAddress.setAddress3( columns[x].trim().replaceAll("@", ",") );
                                break;
                            case 7: //Ship to City 1
                                if ( !ignoreAddress )
                                    shippingAddress.setCity( columns[x].trim().replaceAll("@", ",") );
                                break;
                            case 0: //Ship to State 1
                                if ( !ignoreAddress )
                                    shippingAddress.setState( columns[x].trim().replaceAll("@", ",") );
                                break;
                            case 1: //Ship to Zipcode 1
                                if ( !ignoreAddress )
                                    shippingAddress.setZipCode( columns[x].trim().replaceAll("@", ",") );
                                break;
                            case 2: //Ship to Country 1
                                if ( !ignoreAddress )
                                    shippingAddress.setCountry( columns[x].trim().replaceAll("@", ",") );
                                break;
                            case 3: //Ship to Sales Tax Code 1
                                //IGNORE...
                                break;
                        }
                    }
                    /*else
                    {
                        log ( "ROW:" + rowNo + ": columns:" + (x+1) + ": VALUE IS EMPTY" );
                    }*/
                }
                if ( columns[84] != null && columns[84].trim().length() >0 ) //Customer Type
                    customerInfo.setDistributor( columns[84].trim().startsWith( "Disti" ) );
                String phoneNo = "";
                if ( columns[85] != null && columns[85].trim().length() >0 ) //Telephone 1
                    phoneNo = columns[85].trim().replaceAll("@", ",");
                if ( columns[86] != null && columns[86].trim().length() >0 ) //Telephone 2
                    phoneNo =  phoneNo + " , " + columns[86].trim().replaceAll("@", ",");
                billingAddress.setPhoneNumber( phoneNo );

                if ( columns[87] != null && columns[87].trim().length() >0 ) //Fax Number
                    billingAddress.setFaxNumber( columns[87].trim().replaceAll("@", ",") );

                if ( columns[88] != null && columns[88].trim().length() >0 ) //Customer E-mail
                    billingAddress.setEmail( columns[88].trim() );

                customerList.add( customerInfo );
            }
            else
            {
                log ( "IGNORING: ROW:" + rowNo + " COULD NOT FOUND CUSTOMER NUMBER / CUSTOMER NAME" );
            }
        }

        try
        {
            br.close();
        }catch (IOException ignore) {}

        return customerList;
    }
}

